package net.zjitc.dao;

import com.mchange.v2.c3p0.impl.DefaultConnectionTester;
import net.zjitc.domain.User;
import net.zjitc.utils.C3P0Utils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import javax.sql.DataSource;
import java.net.ConnectException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2017/9/18.
 */
public class UserDao {
    public int PageSize=3;
    public List<User> findPage(int page) throws SQLException {
  //链接数据库
        Connection connection=C3P0Utils.getConneciton();
        //sql
        String sql="select *from user order by id limit ?,?";
        PreparedStatement ps=connection.prepareStatement(sql);
        ps.setInt(1,(page-1)*PageSize);
        ps.setInt(2,PageSize);
        ResultSet result=ps.executeQuery();
        //使用集合存放查出来的对象
        List<User>list=new ArrayList<>();
        while(result.next()){
            User user=new User();
            user.setId(result.getInt(1));
            user.setName(result.getString(2));
            user.setPassword(result.getString(3));
            list.add(user);

        }

  return list;
    }
    public List<User> findAllByUtils() throws SQLException {
        //执行SQL的对象
        QueryRunner runner=new QueryRunner(C3P0Utils.getDataSourse());
        String sql="select*from user";
        //使用封装策略自动封装
        //结果集的封装策略，ResultSetHandler类
        //因为结果有不同的各类，也可能是一个集合，也可能是一个对象
        //所以使用ResultSetHandle类的子类，封装集合的是BeanlistHandler，而封闭一个对象的BeanHandle子类
        //User.Class是封装的参考模型。JavaBean
        List list=(List)runner.query(sql,new BeanListHandler<>(User.class));
        return list;
    }
    /**
     * 按ID查询，注意结果集的封装
     *
     * */
    public User findById(int id) throws SQLException {
        QueryRunner runner =new QueryRunner(C3P0Utils.getDataSourse());
        String sql="select*from User where id=?";
        User user=(User)runner.query(sql,new BeanListHandler<>(User.class),id);
        return user;
    }


}
